<?php
require 'TVB/pms/pdes/common/bootstrap.php';
require 'TVB/pms/pdes/common/util/UpdateMeta.class.php';
require 'TVB/ImageInfrastructure/Client/requires.php';
require 'TVB/VideoInfrastructure/Client/requires.php';
require_once 'TVB/pms/pdes/common/util/ChangeLogTools.class.php';

empty($_REQUEST['action']) && red_error(2,'action is null');

// common functions
function red_error($errno,$msg=array(),$show=1){/*{{{*/
	if(!is_array($msg)) $msg = array($msg);
    $array =array(
        'status'=>'-1',
		'show'=>$show,
        'message'=>$msg,
    );
    switch($errno){
    case 1: array_unshift($array['message'] , 'Data Error'); break;
    case 2: array_unshift($array['message'] , 'Parameters Error'); break;
    case 3: array_unshift($array['message'] , 'Network Error'); break;
    case 4: array_unshift($array['message'] , 'Error'); break;
    }
    exit(json_encode($array));
}
function red_data($data,$title=null,$show=0){
	$json = array('status'=>1,'data'=>$data);
	if($title) $json['title'] = $title;
	if($show){
		$json['show']    = $show;
		$json['message'] = $data;
	}
    exit(json_encode($json));
}
function _get_programme_id($caller_type,$id){
	if(!in_array($caller_type,array('Programme','Episode','Cast'))) red_error(2,'type error');
	switch($caller_type){
	case 'Programme':
		return $id;
	break;
	case 'Episode':
		$obj = new episode();
		$obj = $obj->get($id);
		return $obj->programme_id;
	break;
	case 'Cast':
		$obj = new cast();
		$obj = $obj->get($id);
		return $obj->programme_id;
	break;
	}
}
/**
 * media_type : video or image
 */
function media_can_by_delete($media_type,$media_id){
	$status         = AC::STATUS_UNPUBLISH_PREMIUM;
	$non_status     = AC::STATUS_UNPUBLISH;

	$publish_logs   = publish_log::getPublishLog($media_type,$media_id);
	$premium_ok     = isset($publish_logs->premium)     ? ($publish_logs->premium->status==$status)         : true;
	$non_premium_ok = isset($publish_logs->non_premium) ? ($publish_logs->non_premium->status==$non_status) : true;

	if( $premium_ok && $non_premium_ok ){
		return true;
	}
	return false;
}
/*}}}*/

// Action
$func_name = $_REQUEST['action'];/*{{{*/
switch ($_REQUEST['action'])
{
case 'get_all':
case 'get_inbox':
    !isset($_REQUEST['id']) ?             red_error(2,'id is null') :              $id = $_REQUEST['id']+0;
    !isset($_REQUEST['folder_type']) ?    red_error(2,'folder_type is null') :     $folder_type = $_REQUEST['folder_type']+0;
    !isset($_REQUEST['type']) ?		      red_error(2,'type is null') :		       $caller_type = $_REQUEST['type'];
	$caller_type = ucfirst(strtolower(trim($caller_type)));
    $func_name($id, $caller_type, $folder_type);
break;
case 'get_selected':
    !isset($_REQUEST['folder']) ?         red_error(3,'folder is null') :          $folderid = $_REQUEST['folder']+0;
    !isset($_REQUEST['folder_type']) ?    red_error(2,'folder_type is null') :     $folder_type = $_REQUEST['folder_type']+0;
    $func_name($folderid,$folder_type);
break;
case 'get_one_by_item_id':
    !isset($_REQUEST['item_id']) ?        red_error(2,'item_id is null') :         $item_id  = $_REQUEST['item_id']+0;
    !isset($_REQUEST['folder']) ?         red_error(2,'folder is null') :          $folderid = $_REQUEST['folder']+0;
    !isset($_REQUEST['folder_type']) ?    red_error(2,'folder_type is null') :     $folder_type = $_REQUEST['folder_type'];
    $func_name($item_id,$folderid,$folder_type);
break;
case 'get_one_by_obj_id':
    !isset($_REQUEST['item_id']) ?        red_error(2,'item_id is null') :         $item_id  = $_REQUEST['item_id']+0;
    !isset($_REQUEST['folder_type']) ?    red_error(2,'folder_type is null') :     $folder_type = $_REQUEST['folder_type']+0;
    $func_name($item_id,$folder_type);
break;
case 'delete_item':
	!isset($_REQUEST['item_id']) ?        red_error(2,'item_id is null') :         $item_id  = $_REQUEST['item_id']; 
    !isset($_REQUEST['folder']) ?         red_error(2,'folder is null') :          $folderid = $_REQUEST['folder']+0;
    !isset($_REQUEST['folder_type']) ?    red_error(2,'folder_type is null') :     $folder_type = $_REQUEST['folder_type']+0;
	$func_name($item_id, $folder_type,$folderid);
break;
case 'update_global_info':
	!isset($_REQUEST['item_id']) ?        red_error(2,'item_id is null') :         $item_id  = $_REQUEST['item_id']; 
    !isset($_REQUEST['folder_type']) ?    red_error(2,'folder_type is null') :     $folder_type = $_REQUEST['folder_type']+0;
    !isset($_REQUEST['data']) ?           red_error(2,'data is null') :            $data   = $_REQUEST['data'];
	$func_name($item_id, $folder_type, json_decode($data,true));
break;
case 'update_local_info':
	!isset($_REQUEST['folder']) ?		  red_error(2,'folder is null') :		   $folderid = $_REQUEST['folder']+0;
	!isset($_REQUEST['item_id']) ?        red_error(2,'item_id is null') :         $item_id  = $_REQUEST['item_id']+0; 
    !isset($_REQUEST['data']) ?           red_error(2,'data is null') :            $data   = $_REQUEST['data'];
    !isset($_REQUEST['id']) ?             red_error(2,'id is null') :              $id = $_REQUEST['id']+0;
    !isset($_REQUEST['type']) ?		      red_error(2,'type is null') :			   $caller_type = $_REQUEST['type'];
	$caller_type = ucfirst(strtolower(trim($caller_type)));
	$func_name($id,$caller_type,$folderid, $item_id, json_decode($data,true));
break;
case 'save_item':
	!isset($_REQUEST['folder']) ?		  red_error(2,'folder is null') :		   $folderid = $_REQUEST['folder']+0;
	!isset($_REQUEST['item_id']) ?        red_error(2,'item_id is null') :         $item_id  = $_REQUEST['item_id']+0; 
    !isset($_REQUEST['data']) ?           red_error(2,'data is null') :            $data   = $_REQUEST['data'];
	$func_name($folderid, $item_id, json_decode($data,true));
break;
case 'select':
    !isset($_REQUEST['id']) ?             red_error(2,'id is null') :              $id = $_REQUEST['id']+0;
    !isset($_REQUEST['type']) ?		      red_error(2,'type is null') :		       $caller_type = $_REQUEST['type'];
	!isset($_REQUEST['item_id']) ?        red_error(2,'item_id is null') :         $item_id  = $_REQUEST['item_id']; 
	!isset($_REQUEST['folder']) ?		  red_error(2,'folder is null') :		   $folderid = $_REQUEST['folder']+0;
    !isset($_REQUEST['folder_type']) ?    red_error(2,'folder_type is null') :     $folder_type = $_REQUEST['folder_type']+0;
	$caller_type = ucfirst(strtolower(trim($caller_type)));
	$func_name($id,$caller_type,$item_id,$folderid, $folder_type);
break;
case 'unselect':
    !isset($_REQUEST['id']) ?             red_error(2,'id is null') :              $id = $_REQUEST['id']+0;
    !isset($_REQUEST['type']) ?		      red_error(2,'type is null') :		       $caller_type = $_REQUEST['type'];
    !isset($_REQUEST['folder_type']) ?    red_error(2,'folder_type is null') :     $folder_type = $_REQUEST['folder_type']+0;
	!isset($_REQUEST['item_id']) ?        red_error(2,'item_id is null') :         $item_id  = $_REQUEST['item_id']; 
	!isset($_REQUEST['folder']) ?		  red_error(2,'folder is null') :		   $folderid = $_REQUEST['folder']+0;
	$caller_type = ucfirst(strtolower(trim($caller_type)));
	$func_name($id,$caller_type,$folder_type,$item_id,$folderid);
break;
case 'save_rank':
    !isset($_REQUEST['id']) ?             red_error(2,'id is null') :              $id = $_REQUEST['id']+0;
    !isset($_REQUEST['type']) ?		      red_error(2,'type is null') :		       $caller_type = $_REQUEST['type'];
	!isset($_REQUEST['folder']) ?		  red_error(2,'folder is null') :		   $folderid = $_REQUEST['folder']+0;
    !isset($_REQUEST['data']) ?           red_error(2,'data is null') :            $data   = $_REQUEST['data'];
    !isset($_REQUEST['folder_type']) ?    red_error(2,'folder_type is null') :     $folder_type = $_REQUEST['folder_type']+0;
	$caller_type = ucfirst(strtolower(trim($caller_type)));
	$func_name($id,$caller_type,$folderid,$data,$folder_type);
break;
}/*}}}*/


// get all items from programme id
function get_all($id, $caller_type, $folder_type)/*{{{*/
{
	$programme_id = _get_programme_id($caller_type, $id);
	$deleted      = AC::STATUS_DELETED;

	if( AC::FOLDER_TYPE_IMAGE == $folder_type){
		$source_field = 'i.ii_id AS src_id';
		$status_field = 'i.ii_status AS status';
		$table_name   = 'image';
	}else if( AC::FOLDER_TYPE_VIDEO == $folder_type ){
		$source_field = 'i.vi_id AS src_id';
		$status_field = 'i.vi_status AS status';
		$table_name   = 'video';
	}

	$sql = "
		SELECT
			i.programme_id, i.id, $source_field, i.modified_at, $status_field, i.thumbnail_url AS image_src, il.description, il.title
		FROM
			$table_name AS i INNER JOIN `{$table_name}-language` AS il ON (i.id = il.{$table_name}_id)
		WHERE 
			(i.programme_id=:pid) AND (i.status<>:status)
		ORDER BY i.modified_at DESC
	";

    $data = $exists = array();
    $rs   = Model::selectBySql($sql,array(':pid'=>$programme_id,':status'=>$deleted));

    foreach($rs as $row){
		if(isset($exists[$row['id']])){
			$index = $exists[$row['id']];
			$data[$index]['title'] .= ($data[$index]['title'] ? ' '.$row['title'] : '');
			continue;
		}else{
			$exists[$row['id']] = count($data);
		}

        $data[] = array(
            'id'       => $row['id'],
            'title'    => $row['title'],
            'short_detail' => mb_strimwidth($row['description'],0,60,'...','utf8'),
            'image_src'=> $row['image_src'],
			'date'     => $row['modified_at'],
			'status'   => $row['status'],
        );
    }
    red_data($data);
}/*}}}*/

// get selected items from folder id
function get_selected($folder_id,$folder_type)/*{{{*/
{
	$deleted = AC::STATUS_DELETED;
	$param   = array(':folder_id'=>$folder_id, ':status'=>$deleted, ':item_type_id'=>$folder_type);

	if( AC::FOLDER_TYPE_IMAGE == $folder_type )
	{
		$item_table   = 'image';
		$status_field = 'i.ii_status as status';
	}
	else if( AC::FOLDER_TYPE_VIDEO == $folder_type )
	{
		$item_table   = 'video';
		$status_field = 'i.vi_status as status';
	}

	$sql = "
		SELECT 
			fil.title, fil.description, fi.rank, i.source_url AS b_url, i.thumbnail_url as image_src, i.modified_at, $status_field, i.id, fil.language_id
		FROM 
			`folder` AS f 
			INNER JOIN `folder_item` AS fi ON (f.id=fi.folder_id) AND (f.folder_item_type_id=:item_type_id)
			INNER JOIN `folder_item-language` AS fil ON (f.id=fil.folder_id) AND (fi.folder_id=fil.folder_id) AND (fi.item_id=fil.item_id)
			INNER JOIN `$item_table` AS i ON (i.id=fi.item_id)
		WHERE
			(fi.folder_id=:folder_id) AND (i.status<>:status)
		ORDER BY fi.rank,fil.item_id,fil.language_id
	";

    $data = array();
    $rs   = Model::selectBySql($sql,$param);

    foreach($rs as $row){
		$short_detail = mb_strimwidth($row['description'],0,60,'...','utf8');
		$total  = count($data);
		if($total && $data[$total-1]['id'] == $row['id']){
			$data[$total-1]['title']       .= ($data[$total-1]['title']       ? ' '.$row['title'] : $row['title']);
			empty($data[$total-1]['short_detail']) && $data[$total-1]['short_detail'] = $short_detail;
			continue;
		}
        $data[] = array(
            'id'       => $row['id'],
			'rank'     => $row['rank'],
            'title'    => $row['title'],
            'short_detail'   => ($row['description'] ? $short_detail : ''),
            'image_src'=> $row['image_src'],
            'b_url'    => $row['b_url'],
            'date'     => $row['modified_at'],
			'status'   => $row['status'],
        );
    }
    $deflang     = language::getLanguageID(AC::DEFAULT_LANGUAGE);
	$folder_lang = Model::$dbo['r']->find('folder_language','folder_id=? and language_id=?',array($folder_id,$deflang));
    red_data($data,$folder_lang[0]->title);
}/*}}}*/

// get item detail from folder_item table
function get_one_by_item_id($item_id,$folder_id,$folder_type)/*{{{*/
{
	$deleted = AC::STATUS_DELETED;
	$param   = array(':item_id'=>$item_id, ':folder_id'=>$folder_id, ':status'=>$deleted, ':item_type_id'=>$folder_type);

	if( AC::FOLDER_TYPE_IMAGE == $folder_type )
	{
		$item_table   = 'image';
		$status_field = 'i.ii_status as status';
	}
	else if( AC::FOLDER_TYPE_VIDEO == $folder_type )
	{
		$item_table   = 'video';
		$status_field = 'i.vi_status as status';
	}

	$sql = "
		SELECT 
			fil.title, fil.description, fi.rank, i.source_url AS b_url, i.thumbnail_url as image_src, i.modified_at, $status_field, i.id, fil.language_id
		FROM 
			`folder` AS f 
			INNER JOIN `folder_item` AS fi ON (f.id=fi.folder_id) AND (f.folder_item_type_id=:item_type_id)
			INNER JOIN `folder_item-language` AS fil ON (f.id=fil.folder_id) AND (fi.folder_id=fil.folder_id) AND (fi.item_id=fil.item_id)
			INNER JOIN `$item_table` AS i ON (i.id=fi.item_id)
		WHERE
			(fi.folder_id=:folder_id) AND (fi.item_id=:item_id) AND (i.status<>:status)
		ORDER BY fi.rank,fil.item_id,fil.language_id
	";

    $data = array();
    $rs   = Model::selectBySql($sql,$param);

    foreach($rs as $row){
		$total  = count($data);
		if($total && $data[$total-1]['id'] == $row['id']){
			$data[$total-1]['en_title']  = $row['title'];
			$data[$total-1]['en_detail'] = $row['description'];
			continue;
		}
        $data[] = array(
            'id'       => $row['id'],
			'rank'     => $row['rank'],
            'title'    => $row['title'],
			'detail'   => $row['description'],
            'image_src'=> $row['image_src'],
            'b_url'    => $row['b_url'],
            'date'     => $row['modified_at'],
			'status'   => $row['status'],
			'en_title' => '',
			'en_detail'=> ''
        );
    }
    red_data($data);
}/*}}}*/

// delete item from pdes & remote server(II|VI)
function _delete_item($table, $item_id, $folder_type, $folderid){/*{{{*/
	$obj = new $table();
	$obj = $obj->get($item_id);
	$obj->status = AC::STATUS_DELETED;
	$obj->modified_at = date("Y-m-d H:i:s");
	$obj->modified_by = CommonUtils::get_user();
	$ok = Model::$dbo['w']->save($obj);
	if(!$ok){
		Model::$dbo['w']->rollBack();
		red_error(3,"update $table status fail");
	}

	// delete folder item and language record
	$item_folders = 'SELECT id FROM folder WHERE folder_item_type_id=:type';
	Model::updateBysql("DELETE FROM `folder_item-language` WHERE item_id=:id AND folder_id IN($item_folders)",array(':type'=>$folder_type,':id'=>$item_id));
	Model::updateBysql("DELETE FROM `folder_item` WHERE item_id=:id AND folder_id IN($item_folders)",array(':type'=>$folder_type,':id'=>$item_id));

	// remove remote file
	if($obj->ii_status != AC::IMAGE_STATUS_FAILED){
		$ii = new ImageInfrastructureClient();
		$ok = $ii->DeleteSource($obj->ii_id);
		if($ok<0){
			Model::$dbo['w']->rollBack();
			red_error(3,'ii server error:delete image fails');
		}
	}

	$folderid = strval($folderid);
	change_log::remove(array('type'=>'folder_item','keys.folder_id'=>$folderid,'keys.item_id'=>$item_id));
	change_log::remove(array('type'=>'folder_item-language','keys.folder_id'=>$folderid,'keys.item_id'=>$item_id));
	change_log::remove(array('type'=>$table,'keys'=>array('id'=>$item_id)));
	change_log::remove(array('type'=>"$table-language","keys.{$table}_id"=>$item_id));

	if('video' == $table){
		Model::updateBySql('delete from `episode-video` where video_id = :a',array(':a'=>$obj->id));
		Model::updateBySql('update chapter set status=:st where video_id = :vid',array(':st'=>AC::STATUS_DELETED,':vid'=>$item_id)); 
		$all_export = Model::selectBySql('select id from video_export where video_id=?',array($item_id));
		foreach($all_export as $export_id){
			change_log::remove(array('type'=>'video_export','keys'=>array('id'=>$export_id['id'])));
		}
		change_log::remove(array('type'=>'video_ppv','keys.video_id'=>$item_id));
		change_log::remove(array('type'=>'video_subscription','keys.video_id'=>$item_id));
		change_log::remove(array('type'=>'video_premium','keys.video_id'=>$item_id));
		change_log::remove(array('type'=>'episode-video','keys.video_id'=>$item_id));
		$chapters = CommonUtils::exec_sql(Model::$dbo['w']->getDbHandle(),'select id from chapter where video_id='.$item_id);
		foreach($chapters as $c){
			change_log::remove(array('type'=>'chapter','keys'=>array('id'=>$c['id'])));
			change_log::remove(array('type'=>'chapter-language','keys.chapter_id'=>$c['id']));
			change_log::remove(array('type'=>'chapter-tvbcom_category','keys.chapter_id'=>$c['id']));
		}

		//delete video 
		if($obj->vi_status != AC::VIDEO_STATUS_FAILED){
			$vi = new VideoInfrastructureClient();
			$ok = $vi->DeleteSource($obj->vi_id);
			if($ok<0){
				Model::$dbo['w']->rollBack();
				red_error(3,'vi server error:delete video fails');
			}
		}
	}
}
function delete_item($item_ids,$folder_type,$folderid)
{
	// delete image
	$status = AC::STATUS_DELETED;
	if( AC::FOLDER_TYPE_IMAGE == $folder_type ){
		$table = 'image';
	}else if( AC::FOLDER_TYPE_VIDEO == $folder_type ){
		$table = 'video';
	}

	$obj = new $table();

	$item_ids = explode(',',$item_ids);
	/*
	foreach($item_ids as $item_id){
		$have = Model::selectBySql('
			SELECT l.status,l.action FROM publish_log l INNER JOIN item_type i ON l.item_type_id=i.id AND i.system_label=:tab_name 
			WHERE l.item_id = :id
			ORDER BY published_at DESC limit 1
			',array(':tab_name'=>$table, ':id'=>$item_id));
		if($have && $have[0]['status'] == AC::STATUS_PUBLISH){
			red_error(3, $table. ':' . $item_id . ' are published. Please unpublish them first. Only list_of_item_id are deleted');
		}
	}
	*/
	Model::$dbo['w']->beginTransaction();
	try{
		$failed = $successful = array();

		foreach($item_ids as $item_id){
			if( media_can_by_delete($table,$item_id) ){
				$successful[] = $item_id;
			}else{
				$failed[] = $item_id;
				continue;
			}

			$obj = $obj->get($item_id);
			
			// remove remote file
			/*
			if($obj->ii_status != AC::IMAGE_STATUS_FAILED){
				$ii = new ImageInfrastructureClient();
				$ok = $ii->DeleteSource($obj->ii_id);
				if($ok<0){
					Model::$dbo['w']->rollBack();
					red_error(3,'ii server error:delete image fails');
				}
			}
			 */
			$field = 'ii_id';
			if('video' == $table){
				/*
				if($obj->vi_status != AC::VIDEO_STATUS_FAILED){
					$vi = new VideoInfrastructureClient();
					$ok = $vi->DeleteSource($obj->vi_id);
					if($ok<0){
						Model::$dbo['w']->rollBack();
						red_error(3,'vi server error:delete video fails');
					}
				}
				 */
				$field = 'vi_id';
			}

			# for clone
			$media_data = Model::selectBySql("select id from $table where $field={$obj->$field}");
			#error_log("select id from $table where $field={$obj->$field}");
			#error_log(print_r($media_data,1));
			foreach($media_data as $row){
				$item_id = $row['id'];
				$obj = $obj->get($item_id);
				$obj->status = $status;
				$obj->modified_at = date("Y-m-d H:i:s");
				$obj->modified_by = CommonUtils::get_user();
				$ok = Model::$dbo['w']->save($obj);
				if(!$ok){
					Model::$dbo['w']->rollBack();
					red_error(3,"update $table status fail");
				}

				if('video' == $table){
					Model::updateBySql('delete from `episode-video` where video_id=?',array($item_id));
					Model::updateBySql('update chapter set status=? where video_id=?',array(AC::STATUS_DELETED,$item_id)); 
					$all_export = Model::selectBySql('select id from video_export where video_id=?',array($item_id));
					foreach($all_export as $export_id){
						change_log::remove(array('type'=>'video_export','keys'=>array('id'=>$export_id['id'])));
					}
					change_log::remove(array('type'=>'video_ppv','keys.video_id'=>$item_id));
					change_log::remove(array('type'=>'video_subscription','keys.video_id'=>$item_id));
					change_log::remove(array('type'=>'video_premium','keys.video_id'=>$item_id));
					change_log::remove(array('type'=>'episode-video','keys.video_id'=>$item_id));
					$chapters = CommonUtils::exec_sql(
						Model::$dbo['w']->getDbHandle(),
						'select id from chapter where video_id='.$item_id
					);
					foreach($chapters as $c){
						change_log::remove(array('type'=>'chapter','keys'=>array('id'=>$c['id'])));
						change_log::remove(array('type'=>'chapter-language','keys.chapter_id'=>$c['id']));
						change_log::remove(array('type'=>'chapter-tvbcom_category','keys.chapter_id'=>$c['id']));
					}
				}
				change_log::remove(array('type'=>$table,'keys'=>array('id'=>$item_id)));
				change_log::remove(array('type'=>"$table-language","keys.{$table}_id"=>$item_id));

				$sql = 'select folder_id from folder f inner join folder_item fi '.
					   'on f.id=fi.folder_id and f.folder_item_type_id=? and fi.item_id=?';
				#error_log($sql.'|'.$folder_type.'|'.$row['id']);
				$all_folder_id = Model::selectBySql($sql,array($folder_type,$row['id']));
				#error_log(print_r($all_folder_id,1));
				foreach($all_folder_id as $folder_data){
					// delete folder item and language record
					$folderid = strval($folder_data['folder_id']);
					#error_log(" $table : $item_id => $folderid ");
					$item_folders = 'SELECT id FROM folder WHERE folder_item_type_id=:type';
					Model::updateBysql(
						"DELETE FROM `folder_item-language` WHERE item_id=:id AND folder_id IN($item_folders)",
						array(':type'=>$folder_type,':id'=>$item_id)
					);
					Model::updateBysql(
						"DELETE FROM `folder_item` WHERE item_id=:id AND folder_id IN($item_folders)",
						array(':type'=>$folder_type,':id'=>$item_id)
					);
					change_log::remove(array('type'=>'folder_item','keys.folder_id'=>$folderid,'keys.item_id'=>$item_id));
					change_log::remove(array('type'=>'folder_item-language','keys.folder_id'=>$folderid,'keys.item_id'=>$item_id));
				}
			}
		}
		folder_item_rearrange($folderid);
		Model::$dbo['w']->commit();
		if($failed){
			$msg = 'Delete "'.$table.'" '.implode(',',$failed).' are published/published(premium), Please unpublish/unpublish(premium) them first.';
			if($successful) $msg .= ' Only '.implode(',',$successful).' are deleted';
			red_data($msg,'',1);
		}else{
			red_data('ok');
		}
	}catch(Exception $e){
		error_log('Red->Function "delete_item" Error Message:'.$e);
		error_log('Red->Function "delete_item" Parameters:'.print_r(func_get_args(),1));
		Model::$dbo['w']->rollBack();
		red_error(1,'system error!');
	}
}/*}}}*/

// update item detail information to folder_item table
function update_local_info($id, $caller_type, $folder_id,$item_id,$data){/*{{{*/
    $en_language = language::getLanguageID(AC::LANGUAGE_EN);
    $zh_language = language::getLanguageID(AC::LANGUAGE_ZH);

	Model::$dbo['w']->beginTransaction();
	try{
		$param = array(':id'=>$folder_id,':item_id'=>$item_id);
		$sql   = 'INSERT INTO `folder_item-language`(folder_id,item_id,language_id,title,description)VALUES(:id,:item_id,:lid,:title,:desc)';
		$sql  .= 'ON DUPLICATE KEY UPDATE title=VALUES(title), description=VALUES(description)';

		$param[':title'] = trim($data['title']);
		$param[':desc']  = $data['desc'];
		$param[':lid']   = $zh_language;
		$i = Model::updateBySql($sql,$param);

		$lang = array($zh_language);
		if(isset($data['en_title']) && isset($data['en_desc'])){
			$param[':title'] = $data['en_title'];
			$param[':desc']  = $data['en_desc'];
			$param[':lid']   = $en_language;
			$j = Model::updateBySql($sql,$param);
			$lang[] = $en_language;
		}
		write_change_log($caller_type,$id,array($item_id),$folder_id,$lang,1,'update');
		Model::$dbo['w']->commit();
	}catch(Exception $e){
		error_log('Red->Function "update_local_info" Error Message:'.$e);
		error_log('Red->Function "update_local_info" Parameters:'.print_r(func_get_args(),1));
		Model::$dbo['w']->rollBack();
		red_error(1,'system error!');
	}

	red_data(1);
}/*}}}*/

// select a item to folder
function select($id, $caller_type, $item_ids,$folder_id,$folder_type){/*{{{*/
    $deflang     = language::getLanguageID(AC::DEFAULT_LANGUAGE);
	$programme_id = _get_programme_id($caller_type, $id);

	if( AC::FOLDER_TYPE_IMAGE == $folder_type ){
		$obj_field = array('image_language','image_id');
		$is_video  = false;
	}else if( AC::FOLDER_TYPE_VIDEO == $folder_type ){
		$obj_field = array('video_language','video_id');
		$is_video  = true;
	}

	$item_ids = explode(',',$item_ids);
	$folder_item = new folder_item();
	$folder_item_lang = new folder_item_language();
	$max_rank = $folder_item->getMaxRank($folder_id);

	Model::$dbo['w']->beginTransaction();
	try{
		$change_logs = array();
		foreach($item_ids as $item_id){
			$have     = Model::$dbo['r']->find('folder_item',"item_id=? and folder_id=?",array($item_id,$folder_id));
			if($have) continue;

			$item_obj = Model::$dbo['r']->find($obj_field[0],"{$obj_field[1]}=? and language_id=?",array($item_id,$deflang));

			$folder_item->folder_id = $folder_id;
			$folder_item->item_id   = $item_id;
			$folder_item->rank      = ++$max_rank;
			Model::$dbo['w']->save($folder_item);

			$folder_item_lang->folder_id   = $folder_id;
			$folder_item_lang->item_id     = $item_id;
			$folder_item_lang->language_id = $deflang;
			$folder_item_lang->title       = $item_obj[0]->title;
			$folder_item_lang->description = $item_obj[0]->description;
			Model::$dbo['w']->save($folder_item_lang);
		}
		$lang = array(language::getLanguageID(AC::LANGUAGE_EN), language::getLanguageID(AC::LANGUAGE_ZH));
		write_change_log($caller_type,$id,$item_ids,$folder_id,$lang,1,'select');

		Model::$dbo['w']->commit();
	}catch(Exception $e){
		error_log('Red->Function "select" Error Message:'.$e);
		error_log('Red->Function "select" Parameters:'.print_r(func_get_args(),1));
		Model::$dbo['w']->rollBack();
		red_error(1,'system error!');
	}
	red_data(1);
}/*}}}*/

// delete a item from folder
function unselect($id, $caller_type, $folder_type, $item_ids,$folder_id){/*{{{*/
	if( AC::FOLDER_TYPE_IMAGE == $folder_type ){
		$is_video = false;
		$tab_name = 'image';
	}else if( AC::FOLDER_TYPE_VIDEO == $folder_type ){
		$is_video  = true;
		$tab_name = 'video';
	}

	$item_ids = explode(',',$item_ids);
	Model::$dbo['w']->beginTransaction();
	try{
		$lang = array(language::getLanguageID(AC::LANGUAGE_EN), language::getLanguageID(AC::LANGUAGE_ZH));
		write_change_log($caller_type,$id,$item_ids,$folder_id,$lang,0,'unselect');

		foreach($item_ids as $item_id){
			Model::$dbo['w']->delete('folder_item_language','(folder_id=?) AND (item_id=?)',array('folder_id'=>$folder_id,'item_id'=>$item_id));
			Model::$dbo['w']->delete('folder_item',         '(folder_id=?) AND (item_id=?)',array('folder_id'=>$folder_id,'item_id'=>$item_id));

			if($is_video && 'Episode' == $caller_type){
				Model::updateBySql('delete from `episode-video` where episode_id = :eid and video_id = :vid',array(':eid'=>$id,':vid'=>$item_id));
				Model::updateBySql('update chapter set status=:st where video_id = :vid',array(':st'=>AC::STATUS_DELETED,':vid'=>$item_id)); 
			}

		}
		folder_item_rearrange($folder_id);
		Model::$dbo['w']->commit();
		red_data('ok');
	}catch(Exception $e){
		error_log('Red->Function "unselect" Error Message:'.$e);
		error_log('Red->Function "unselect" Parameters:'.print_r(func_get_args(),1));
		Model::$dbo['w']->rollBack();
		red_error(1,'system error!');
	}
}/*}}}*/

// save rank for all folder items 
function save_rank($id, $caller_type, $folder_id, $data, $folder_type){/*{{{*/
	if(empty($data)) red_data(1);

	if( AC::FOLDER_TYPE_IMAGE == $folder_type ){
		$sql = 'SELECT fi.* FROM folder_item fi INNER JOIN image i ON fi.item_id=i.id WHERE fi.folder_id='.$folder_id.' AND i.status <> '.AC::STATUS_DELETED;
	}else if( AC::FOLDER_TYPE_VIDEO == $folder_type ){
		$sql = 'SELECT fi.* FROM folder_item fi INNER JOIN video v ON fi.item_id=v.id WHERE fi.folder_id='.$folder_id.' AND v.status <> '.AC::STATUS_DELETED;
	}
	$folder_items = Model::$dbo['w']->findBySql('folder_item',$sql);//,array(':fid'=>$folder_id,':st'=>AC::STATUS_DELETED));

	$data  = explode(',',$data);
	$items = array();
	foreach($data as $d){
		$item = explode('=',$d);
		$items[$item[0]] = $item[1];
	}

	Model::$dbo['w']->beginTransaction();
	try{
		if($folder_items){  //for bug:7429        #modified by Albie Lu on 2011-10-21 
			$change_logs = array();
			$programme_id = _get_programme_id($caller_type, $id);
			foreach($folder_items as $folder_item){
				if( $items[$folder_item->item_id]!=$folder_item->rank){
					$change_logs[] = array(
						'type' => 'folder_item',
						'keys' => array('folder_id'=>$folder_id,'item_id'=>$folder_item->item_id),
						'programme_id' => $programme_id,
						'is_publish' => 0,
						'is_active' => 1,
					);
				}
				$folder_item->rank = empty($items[$folder_item->item_id]) ? $folder_item->getMaxRank($folder_id) : $items[$folder_item->item_id];
				Model::$dbo['w']->save_by_fields($folder_item,array('folder_id','item_id'));
				
			}
		    array_walk($change_logs,'change_log::updateOne');
		}

		Model::$dbo['w']->commit();
	}catch(Exception $e){
		error_log('Red->Function "save_rank" Error Message:'.$e);
		error_log('Red->Function "save_rank" Parameters:'.print_r(func_get_args(),1));
		Model::$dbo['w']->rollBack();
		red_error(1,'system error!');
	}
	red_data(1);
}/*}}}*/


function folder_item_rearrange($folder_id){
	$folder_items = Model::$dbo['w']->findBySql('folder_item',"select * from folder_item where folder_id=$folder_id order by rank");
	foreach($folder_items as $index => $row){
		$row->rank = $index+1;
		Model::$dbo['w']->save_by_fields($row,array('folder_id','item_id'));
	}
}

function write_change_log($caller_type,$id,$item_ids,$folder_id,array $lang,$is_active=1,$type='select'){
	$programme_id = _get_programme_id($caller_type, $id);
    $deflang      = language::getLanguageID(AC::DEFAULT_LANGUAGE);
	$change_logs  = array();
	$is_image     = folder::is_image_folder($folder_id);
	$db_handle    = Model::$dbo['w']->getDbHandle();

	$is_select_in  = ($type == 'select');
	$is_select_out = ($type == 'unselect');
	$is_update     = ($type == 'update');

	foreach($item_ids as $item_id){
		$folder_data = $change_data = array(); 

		# folder_item and folder_item-language's change_logs
		if($is_select_out){
			Model::$dbo['w']->delete('folder_item_language','(folder_id=?) AND (item_id=?)',array('folder_id'=>$folder_id,'item_id'=>$item_id));
			Model::$dbo['w']->delete('folder_item',         '(folder_id=?) AND (item_id=?)',array('folder_id'=>$folder_id,'item_id'=>$item_id));
		}
		if($caller_type == 'Programme') 
		{
			$folder_data = array('programme_id'=>$programme_id,'episode_id'=>array());
			$change_data = $is_image ? ChangeLogTools::getEpisodeIdsByImageId($item_id) : $folder_data;
		}
		elseif($caller_type == 'Episode')
		{
			$folder_data = array('programme_id'=>0,'episode_id'=>array($id));
			$change_data = ChangeLogTools::getEpisodeIdsByImageId($item_id);
		}
		elseif($caller_type == 'Cast')
		{
			$folder_data = ChangeLogTools::getEpisodeIdsByCastId($id);
			$change_data = ChangeLogTools::getEpisodeIdsByImageId($item_id);
		}
		$folder_data['is_publish'] = $change_data['is_publish'] = 0;
		$folder_data['is_active']  = $change_data['is_active']  = $is_active;

		# for bug 7307,7501, add folder & folder-language  to change_log
		#
		### folder, image_folder-config, folder-language, folder_item, folder_item-language
		$folder_data['type'] = 'folder';
		$folder_data['keys'] = array('id'=>$folder_id);
		$folder_data['is_active'] = 1;
		$change_logs[] = $folder_data;     ## folder
		if($is_image){
			$folder_data['type'] = 'image_folder-config';
			$folder_data['keys'] = array('folder_id'=>$folder_id);
			$change_logs[] = $folder_data; ## image_folder-config
		}
		if($caller_type == 'Cast'){ # for bug 7680
			$folder_data['type'] = 'cast';
			$folder_data['keys'] = array('id'=>$id);
			$change_logs[] = $folder_data;
			foreach($lang as $l){
				$folder_data['type'] = 'cast-language';
				$folder_data['keys'] = array('cast_id'=>$id,'language_id'=>$l);
				$change_logs[] = $folder_data;
			}
		}
		$folder_data['type'] = 'folder_item';
		$folder_data['keys'] = array('folder_id'=>$folder_id,'item_id'=>$item_id);
		$folder_data['is_active'] = $is_active;
		$change_logs[] = $folder_data;     ## folder_item
		foreach($lang as $l){
			$folder_data['type'] = 'folder-language';
			$folder_data['keys'] = array('folder_id'=>$folder_id,'language_id'=>$l);
			$folder_data['is_active'] = 1;
			$change_logs[] = $folder_data; ## folder-language
			$folder_data['type'] = 'folder_item-language';
			$folder_data['keys'] = array('folder_id'=>$folder_id,'item_id'=>$item_id,'language_id'=>$l);
			$folder_data['is_active'] = $is_active;
			$change_logs[] = $folder_data; ## folder_item-language
		}


		# 是否有其它 folder 引用了它
		if($is_image){ # 2
			$relate_sql='
				select m.id from image m,folder_item fi,folder f 
				where f.folder_item_type_id='.AC::FOLDER_TYPE_IMAGE.' and m.id=fi.item_id and m.status=? and m.id=? and fi.folder_id<>?
			';
		}else{ # 1
			$relate_sql='
				select m.id from video m,folder_item fi,folder f 
				where f.folder_item_type_id='.AC::FOLDER_TYPE_VIDEO.' and m.id=fi.item_id and m.status=? and m.id=? and fi.folder_id<>?
			';
		}
		$have_relate = CommonUtils::exec_sql($db_handle,$relate_sql,array(AC::STATUS_NEW,$item_id,$folder_id));

		# (移进:相当于编辑了它) 或者 (移出并且它没有被引用:相当于删除了它) # 或者 (移出并且是属于Episode的时候)
		if($is_select_in || ($is_select_out && !$have_relate) || ($is_select_out && in_array($caller_type,array('Episode','Cast')))){
			$table = $is_image ? 'image' : 'video';
			$change_data['is_active'] = ($have_relate || $is_select_in) ? 1 : 0;
			$change_data['type'] = $table;
			$change_data['keys'] = array('id'=>$item_id);
			$change_logs[] = $change_data;
			foreach($lang as $l){
				$change_data['type'] = "$table-language";
				$change_data['keys'] = array("{$table}_id"=>$item_id,'language_id'=>$l);
				$change_logs[] = $change_data;
			}
			if(!$is_image){
				write_video_change_log($db_handle, $item_id, $change_data, $lang, $change_logs);
			}
		}
	}
	return array_walk($change_logs,'change_log::updateOne');
}

function write_video_change_log($db_handle, $video_id, $change_data, $lang, &$change_logs){
	$video_tables = array(
		'video_premium'      => 'select * from video_premium where video_id=?',
		'video_ppv'          => 'select * from video_ppv where video_id=?',
		'video_subscription' => 'select * from video_subscription where video_id=?',
		'video_export'       => 'select * from video_export where video_id=?',
		'episode-video'      => 'select * from `episode-video` where video_id=?'
	);

	foreach($video_tables as $_t => $_s){
		$_data = CommonUtils::exec_sql($db_handle,$_s,array($video_id));
		if($_data){
			if('episode-video' == $_t){
				$change_data['type'] = 'episode-video';
				$change_data['keys'] = array('episode_id'=>$_data[0]['episode_id'],'video_id'=>$video_id);
				$change_logs[] = $change_data;
			}elseif('video_export' == $_t){   //for bug7382       Albie Lu
				$sql = "select id from video_export where video_id=?";
				$arr = array($video_id);
				$result_data = CommonUtils::exec_sql($db_handle,$sql,$arr);
				foreach($result_data as $v){
					$change_data['type'] = $_t;
					$change_data['keys'] = array('id'=>$v['id']);
					$change_logs[] = $change_data;
				}
			}else{
				$change_data['type'] = $_t;
				$change_data['keys'] = array('video_id'=>$video_id);
				$change_logs[] = $change_data;
			}
		}
	}

	# chapter 的信息
	$chapters = CommonUtils::exec_sql($db_handle,'select id from chapter where video_id='.$video_id);
	foreach($chapters as $c){
		$change_data['type'] = 'chapter';
		$change_data['keys'] = array('id'=>$c['id']);
		$change_logs[] = $change_data;
		# chapter-language 信息
		foreach($lang as $l){
			$change_data['type'] = 'chapter-language';
			$change_data['keys'] = array('chapter_id'=>$c['id'],'language_id'=>$l);
			$change_logs[] = $change_data;
		}
		# 每个 chapter 的 tvbcom_category 信息，如果有的话
		$c_tvbcom = CommonUtils::exec_sql($db_handle,'select * from `chapter-tvbcom_category` where chapter_id='.$c['id']);
		foreach($c_tvbcom as $tvbcom){
			$change_data['type'] = 'chapter-tvbcom_category';
			$change_data['keys'] = array('chapter_id'=>$c['id'],'category_id'=>$tvbcom['category_id']);
			$change_logs[] = $change_data;
		}
	}
}

if(false){
// Unused save a new item to folder_item table
function save_item($folder_id,$item_id,$data){/*{{{*/
	print_r($data);
    $deflang  = language::getLanguageID(AC::DEFAULT_LANGUAGE);

	Model::$dbo['w']->beginTransaction();
	try{
		Model::$dbo['w']->delete('folder_item_language','(folder_id=?) AND (item_id=?)',array('folder_id'=>$folder_id,'item_id'=>$item_id));
		Model::$dbo['w']->delete('folder_item',         '(folder_id=?) AND (item_id=?)',array('folder_id'=>$folder_id,'item_id'=>$item_id));

		$folder_item      = new folder_item();
		$folder_item_lang = new folder_item_language();
		$folder_item->folder_id = $folder_id;
		$folder_item->item_id = $item_id;
		$folder_item->rank    = $data['rank'];
		Model::$dbo['w']->save($folder_item);
		
		$folder_item_lang->folder_id   = $folder_id;
		$folder_item_lang->item_id     = $item_id;
		$folder_item_lang->language_id = $deflang;
		$folder_item_lang->title       = $data['title'];
		$folder_item_lang->description = $data['desc'];

		Model::$dbo['w']->save($folder_item_lang);
		Model::$dbo['w']->commit();
	}catch(Exception $e){
		error_log('Red->Function "save_item" Error Message:'.$e);
		error_log('Red->Function "save_item" Parameters:'.print_r(func_get_args(),1));
		Model::$dbo['w']->rollBack();
		red_error(1,'system error!');
	}

	red_data(1);
}/*}}}*/

// Unused # get item detail from item(image|video) table
function get_one_by_obj_id($id,$folder_type)/*{{{*/
{
    $deflang      = language::getLanguageID(AC::DEFAULT_LANGUAGE);

	$table_name   = 'image';
	$deleted      = AC::STATUS_DELETED;

	if( AC::FOLDER_TYPE_IMAGE == $folder_type ){
        $sql = "
			SELECT 
				il.title, il.description, i.thumbnail_url AS image_src, i.modified_at, i.ii_status as status
			FROM 
				`image` AS i 
				INNER JOIN `image-language` as il ON(i.id=il.image_id)
			WHERE
				(i.id=$id) AND (il.language_id=$deflang) AND(i.status<>$deleted)
            ";
	}else if( AC::FOLDER_TYPE_VIDEO == $folder_type ){
		$table_name   = 'video';

        $sql = "
			SELECT 
				vl.title, vl.description, v.thumbnail_url AS image_src, v.modified_at, v.vi_status as status
			FROM 
				`video` AS v 
				INNER JOIN `video-language` as vl ON(v.id=vl.video_id)
			WHERE
				(v.id=$id) AND (vl.language_id=$deflang) AND (v.status<>$deleted)
            ";
    }
    $data = array();
    $rs   = Model::$dbo['r']->findBySql($table_name,$sql);

    foreach($rs as $row){
        $data[] = array(
            'id'       => $id,
            'title'    => $row->title,
            'short_detail'   => mb_strimwidth($row->description,0,60,'...','utf8'),
            'detail'   => $row->description,
            'image_src'    => $row->image_src,
            'date'    => $row->modified_at,
			'status'   => $row->status,
        );
    }
    red_data($data);
}/*}}}*/

// Unused
function get_inbox($id, $caller_type, $folder_type)/*{{{*/
{
    $deflang      = language::getLanguageID(AC::DEFAULT_LANGUAGE);
	$programme_id = _get_programme_id($caller_type,$id);

	$table_name   = 'image';
	$deleted      = AC::STATUS_DELETED;

	if( AC::FOLDER_TYPE_IMAGE == $folder_type ){
        $sql = "
            SELECT
                i.programme_id, i.id, i.ii_id as src_id, i.thumbnail_url as image_src, i.modified_at, i.ii_status as status
                , il.description, il.title
            FROM
                image AS i
                INNER JOIN `image-language` AS il ON (i.id = il.image_id)
            WHERE 
                (il.language_id=$deflang) AND (i.programme_id=$programme_id) AND (i.status<>$deleted)
				AND ( i.id NOT IN( 
						SELECT DISTINCT item_id FROM image i INNER JOIN folder_item fi ON i.id=fi.item_id
					)
				)
			ORDER BY i.modified_at DESC
            ";
	}else if( AC::FOLDER_TYPE_VIDEO == $folder_type ){
		$table_name   = 'video';

        $sql = "
            SELECT
                v.programme_id, v.id, v.vi_id as src_id, v.thumbnail_url as image_src, v.modified_at, v.vi_status as status
                , vl.description, vl.title
            FROM
                video AS v
                INNER JOIN `video-language` AS vl ON (v.id = vl.video_id)
            WHERE
                (vl.language_id=$deflang) AND (v.programme_id=$programme_id) AND (v.status<>$deleted)
				AND ( v.id NOT IN( 
						SELECT DISTINCT item_id FROM video v INNER JOIN folder_item fi ON v.id=fi.item_id
					)
				)
			ORDER BY v.modified_at DESC
            ";
    }
    $data = array();
    $rs   = Model::$dbo['r']->findBySql($table_name,$sql);

    foreach($rs as $row){
        $data[] = array(
            'id'       => $row->id,
            'title'    => $row->title,
            'short_detail'   => mb_strimwidth($row->description,0,60,'...','utf8'),
            //'src_id'   => $row->src_id,
            'image_src'    => $row->image_src,
            'date'    => $row->modified_at,
			'status'   => $row->status,
        );
    }
    red_data($data);
}/*}}}*/

// Unused # update item(image|video) detail information 
function update_global_info($obj_id,$folder_type,$data){/*{{{*/
    $deflang   = language::getLanguageID(AC::DEFAULT_LANGUAGE);

	if( AC::FOLDER_TYPE_IMAGE==$folder_type ){
		$obj_field = array('image_language','image_id');
	}else if(AC::FOLDER_TYPE_VIDEO==$folder_type ){
		$obj_field = array('video_language','video_id');
	}

	$obj = Model::$dbo['r']->find($obj_field[0],"{$obj_field[1]}=? and language_id=?",array($obj_id,$deflang));

	$obj->title       = $data['title'];
	$obj->description = $data['desc'];
	$obj->$obj_field[1]= $obj_id;
	$obj->language_id = $deflang;

	$rs = Model::$dbo['w']->save_by_fields($obj[0],array($obj_field[1], 'language_id'));
	if(!$rs) red_error(3,'save fail');

	red_data(1);
}/*}}}*/
}
